package com.seu.vcampus.dao;

import com.seu.vcampus.vo.BorrowRecord;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 借阅记录数据访问类
 * 负责借阅记录的CRUD操作
 * 
 * @author 虚拟校园开发团队
 * @since 1.0.0
 */
public class BorrowRecordDao {
    private final Connection connection;
    
    public BorrowRecordDao(Connection connection) {
        this.connection = connection;
        initializeTable();
    }
    
    /**
     * 初始化借阅记录表
     */
    private void initializeTable() {
        try {
            String sql = "CREATE TABLE IF NOT EXISTS borrow_records (" +
                       "record_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                       "user_id VARCHAR(20) NOT NULL," +
                       "book_id VARCHAR(20) NOT NULL," +
                       "borrow_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                       "return_time TIMESTAMP," +
                       "status VARCHAR(10) DEFAULT '借阅中'," +
                       "FOREIGN KEY (user_id) REFERENCES users(user_id)," +
                       "FOREIGN KEY (book_id) REFERENCES books(book_id)" +
                       ")";
            
            try (Statement stmt = connection.createStatement()) {
                stmt.execute(sql);
            }
            
        } catch (SQLException e) {
            System.err.println("初始化借阅记录表失败: " + e.getMessage());
        }
    }
    
    /**
     * 创建借阅记录
     */
    public boolean createBorrowRecord(String userId, String bookId) {
        String sql = "INSERT INTO borrow_records (user_id, book_id) VALUES (?, ?)";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            pstmt.setString(2, bookId);
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
            
        } catch (SQLException e) {
            System.err.println("创建借阅记录失败: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * 归还图书
     */
    public boolean returnBook(int recordId) {
        String sql = "UPDATE borrow_records SET return_time = CURRENT_TIMESTAMP, status = '已归还' WHERE record_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, recordId);
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
            
        } catch (SQLException e) {
            System.err.println("归还图书失败: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * 获取用户的借阅记录
     */
    public List<BorrowRecord> getBorrowRecordsByUserId(String userId) {
        List<BorrowRecord> records = new ArrayList<>();
        String sql = "SELECT r.record_id, r.user_id, r.book_id, r.borrow_time, r.return_time, r.status, " +
                    "b.title, b.author " +
                    "FROM borrow_records r " +
                    "JOIN books b ON r.book_id = b.book_id " +
                    "WHERE r.user_id = ? " +
                    "ORDER BY r.borrow_time DESC";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    BorrowRecord record = new BorrowRecord();
                    record.setRecordId(String.valueOf(rs.getInt("record_id")));
                    record.setUserId(rs.getString("user_id"));
                    record.setBookId(rs.getString("book_id"));
                    record.setBorrowDate(rs.getTimestamp("borrow_time"));
                    record.setReturnDate(rs.getTimestamp("return_time"));
                    record.setStatus(rs.getString("status"));
                    record.setBookName(rs.getString("title"));
                    record.setBookAuthor(rs.getString("author"));
                    records.add(record);
                }
            }
            
        } catch (SQLException e) {
            System.err.println("获取借阅记录失败: " + e.getMessage());
        }
        
        return records;
    }
    
    /**
     * 获取用户当前借阅中的记录
     */
    public List<BorrowRecord> getCurrentBorrowRecords(String userId) {
        List<BorrowRecord> records = new ArrayList<>();
        String sql = "SELECT r.record_id, r.user_id, r.book_id, r.borrow_time, r.return_time, r.status, " +
                    "b.title, b.author " +
                    "FROM borrow_records r " +
                    "JOIN books b ON r.book_id = b.book_id " +
                    "WHERE r.user_id = ? AND r.status = '借阅中' " +
                    "ORDER BY r.borrow_time DESC";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    BorrowRecord record = new BorrowRecord();
                    record.setRecordId(String.valueOf(rs.getInt("record_id")));
                    record.setUserId(rs.getString("user_id"));
                    record.setBookId(rs.getString("book_id"));
                    record.setBorrowDate(rs.getTimestamp("borrow_time"));
                    record.setReturnDate(rs.getTimestamp("return_time"));
                    record.setStatus(rs.getString("status"));
                    record.setBookName(rs.getString("title"));
                    record.setBookAuthor(rs.getString("author"));
                    records.add(record);
                }
            }
            
        } catch (SQLException e) {
            System.err.println("获取当前借阅记录失败: " + e.getMessage());
        }
        
        return records;
    }
    
    /**
     * 检查用户是否已经借阅某本书
     */
    public boolean hasBorrowedBook(String userId, String bookId) {
        String sql = "SELECT COUNT(*) FROM borrow_records WHERE user_id = ? AND book_id = ? AND status = '借阅中'";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            pstmt.setString(2, bookId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
            
        } catch (SQLException e) {
            System.err.println("检查借阅状态失败: " + e.getMessage());
        }
        
        return false;
    }
    
    /**
     * 根据ID获取借阅记录
     */
    public BorrowRecord getBorrowRecordById(int recordId) {
        String sql = "SELECT r.record_id, r.user_id, r.book_id, r.borrow_time, r.return_time, r.status, " +
                    "b.title, b.author " +
                    "FROM borrow_records r " +
                    "JOIN books b ON r.book_id = b.book_id " +
                    "WHERE r.record_id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, recordId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    BorrowRecord record = new BorrowRecord();
                    record.setRecordId(String.valueOf(rs.getInt("record_id")));
                    record.setUserId(rs.getString("user_id"));
                    record.setBookId(rs.getString("book_id"));
                    record.setBorrowDate(rs.getTimestamp("borrow_time"));
                    record.setReturnDate(rs.getTimestamp("return_time"));
                    record.setStatus(rs.getString("status"));
                    record.setBookName(rs.getString("title"));
                    record.setBookAuthor(rs.getString("author"));
                    return record;
                }
            }
            
        } catch (SQLException e) {
            System.err.println("获取借阅记录失败: " + e.getMessage());
        }
        
        return null;
    }
    
    /**
     * 获取借阅记录数量
     */
    public int getBorrowCount(String userId) {
        String sql = "SELECT COUNT(*) FROM borrow_records WHERE user_id = ? AND status = '借阅中'";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, userId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
            
        } catch (SQLException e) {
            System.err.println("获取借阅数量失败: " + e.getMessage());
        }
        
        return 0;
    }
    
    /**
     * 获取所有借阅记录（管理员专用）
     */
    public List<BorrowRecord> getAllBorrowRecords() {
        List<BorrowRecord> records = new ArrayList<>();
        String sql = "SELECT r.record_id, r.user_id, r.book_id, r.borrow_time, r.return_time, r.status, " +
                    "b.title, b.author " +
                    "FROM borrow_records r " +
                    "JOIN books b ON r.book_id = b.book_id " +
                    "ORDER BY r.borrow_time DESC";
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                BorrowRecord record = new BorrowRecord();
                record.setRecordId(String.valueOf(rs.getInt("record_id")));
                record.setUserId(rs.getString("user_id"));
                record.setBookId(rs.getString("book_id"));
                record.setBorrowDate(rs.getTimestamp("borrow_time"));
                record.setReturnDate(rs.getTimestamp("return_time"));
                record.setStatus(rs.getString("status"));
                record.setBookName(rs.getString("title"));
                record.setBookAuthor(rs.getString("author"));
                records.add(record);
            }
            
        } catch (SQLException e) {
            System.err.println("获取所有借阅记录失败: " + e.getMessage());
        }
        
        return records;
    }
    
    /**
     * 检查图书是否有活跃的借阅记录
     */
    public boolean hasActiveBorrowRecords(String bookId) {
        String sql = "SELECT COUNT(*) FROM borrow_records WHERE book_id = ? AND status = '借阅中'";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, bookId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
            
        } catch (SQLException e) {
            System.err.println("检查活跃借阅记录失败: " + e.getMessage());
        }
        
        return false;
    }
}